<?php
// Define the base exception class (as a catch all for the API)
class exceptionEnrolment extends Exception
{
}

class Enrolment extends cart
{
    protected $errorMsg = 'Undefined Error';

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Return error message.
     *
     * @return string
     */
    public function getErrorMessage()
    {
        return $this->errorMsg;
    }

    /**
     * Add a single product with attributes to cart, checking whether it already exists, price difference and availability.
     *
     * @param int    $productId
     * @param array  $AttributesArr
     * @param int    $quantity
     * @param float  $price
     * @param int    $cartId
     * @param int    $category
     * @param int    $isClass
     * @param string $enrolment_type
     * @param bool   $backend
     *
     * @return string
     */
    public function addToCart($productId, $AttributesArr, $quantity, $price, $cartId = null, $category = 0, $isClass = false, $enrolment_type = 'S', $backend = false, $customProductDescription = null)
    {
        global $DBobject;

        if (!$isClass) {
            return parent::addToCart($productId, $AttributesArr, $quantity, $price, $cartId, $category);
        }

        if (empty($cartId)) {
            if (empty($this->cart_id)) {
                $this->__construct();
            }
            $cartId = $this->cart_id;
        }

        $quantity = intval($quantity);
        $price = floatval($price);
        $message = '';

        $classTermObj = new ClassTermClass();
        $classData = $classTermObj->GetClassTermData($productId, false);

        if (strtotime($classData['term_end_date']) < time() || empty($AttributesArr['week'][0])) {
            return "课程 '{$classData['class_name']}' 已停止报名。 学期已经结束。";
        }

        if (empty($classData['availability']['available']) && !$backend) {
            return "课程 '{$classData['class_name']}' 已经停止报名。";
        }

        $weeks = array();

        $tempAttrArr = array();
        $tempCnt = 0;
        foreach ($AttributesArr['week'] as $wk) {
            if (!empty($wk)) {
                $weeks[] = $wk;
                $tempAttrArr[$tempCnt]['attribute_name'] = 'week';
                $tempAttrArr[$tempCnt]['attr_value_name'] = $wk;
                ++$tempCnt;
            }
        }

        $sessions = $classData['availability']['weeks'];

        $classPrice = count($weeks) * count($classData['classtimes']) * ((floatval($classData['class_special_price']) > 0) ? floatval($classData['class_special_price']) : floatval($classData['class_price']));

        /* if(($classPrice != $price) || (count($weeks) != count($sessions))){
            $message = "The price of '{$classData['class_name']}' has been updated. ";
        } */

        $trialStr = '';
        if ($enrolment_type == 'T') {
            $trialStr = '体验课 - ';
            $classPrice = 0;
            $cartItem = $this->ProductOnCart($productId, $tempAttrArr, array('params' => array(':type' => 'T'), 'sql' => array('cartitem_enrolment_type = :type')));
        } else {
            $cartItem = $this->ProductOnCart($productId, $tempAttrArr, array('params' => array(':type' => 'S'), 'sql' => array('cartitem_enrolment_type = :type')));
        }

        if ($cartItem['cartitem_id'] == 0) {
            $subtotal = floatval($quantity) * floatval($classPrice);
            $params = [
                ':cid' => $cartId,
                ':product_id' => $productId,
                ':listing_id' => $category,
                ':product_name' => $trialStr.$classData['class_name'].' | '.$classData['term_name'],
                ':enrolment_type' => $enrolment_type,
                ':product_price' => $classPrice,
                ':qty' => $quantity,
                ':subtotal' => $subtotal,
                ':product_gst' => 1,
                ':ip' => $_SERVER['REMOTE_ADDR'],
                ':browser' => $_SERVER['HTTP_USER_AGENT'],
            ];
            $sql = 'INSERT INTO tbl_cartitem (
                cartitem_cart_id,
                cartitem_product_id,
                cartitem_listing_id,
                cartitem_product_name,
                cartitem_product_price,
                cartitem_quantity,
                cartitem_subtotal,
                cartitem_product_gst,
                cartitem_class_flag,
                cartitem_enrolment_type,
                cartitem_user_ip,
                cartitem_user_browser,
                cartitem_created
                ) values(
                :cid,
                :product_id,
                :listing_id,
                :product_name,
                :product_price,
                :qty,
                :subtotal,
                :product_gst,
                1,
                :enrolment_type,
                :ip,
                :browser,
                now()
                )';
            if ($res = $DBobject->wrappedSql($sql, $params)) {
                $errorCnt = 0;
                $cartitem_id = $DBobject->wrappedSqlIdentity();
                foreach ($weeks as $attr) {
                    $params = array(
                            ':cid' => $cartitem_id,
                            ':attribute_name' => 'week',
                            ':attr_value_name' => $attr,
                    );
                    $sql = 'INSERT INTO tbl_cartitem_attr (cartitem_attr_cartitem_id,cartitem_attr_attribute_name,cartitem_attr_attr_value_name,cartitem_attr_created) VALUES (:cid, :attribute_name, :attr_value_name, now())';

                    $res2 = $DBobject->wrappedSql($sql, $params);
                    if (!$res2) {
                        ++$errorCnt;
                    }
                }
                if ($errorCnt == 0) {
                    return "课程\"{$classData['class_name']}\" 已经添加到购物车。{$message}";
                }
            }
        } else {
            /* return "'{$classData['class_name']}' was already added. {$message}"; */
                $quantity = intval($cartItem['cartitem_quantity']) + $quantity;
            if (($quantity > $classData['availability']['available']) && !$backend) {
                return "This class only has {$classData['availability']['available']} spot(s) available. {$message}";
            }

            $params = [
                ':id' => $cartItem['cartitem_id'],
                ':qty' => $quantity,
                ':price' => $classPrice,
                ':subtotal' => $quantity * $classPrice,
                ':listing_id' => $category,
            ];
            $sql = 'UPDATE tbl_cartitem SET cartitem_quantity = :qty, cartitem_product_price = :price, cartitem_subtotal = :subtotal, cartitem_listing_id = :listing_id, cartitem_modified = now()
                    WHERE cartitem_id = :id';

            if ($DBobject->wrappedSql($sql, $params)) {
                return "课程已经成功添加到您的购物车。 {$message}";
            }
        }

        return false;
    }

    public function addCustomProductToCart($productId, $ProductDescription, $price, $attributes = array())
    {
        global $DBobject;

        // Get type
        $csql = 'SELECT paytype_name FROM tbl_paytype WHERE paytype_deleted IS NULL AND paytype_id = :id';
        if ($cres = $DBobject->wrappedSql($csql, array(':id' => $productId))) {
            $paytypeName = $cres[0]['paytype_name'];
        }

        $sql = 'SELECT cartitem_id FROM tbl_cartitem WHERE cartitem_cart_id = :cartitem_cart_id AND cartitem_product_id = :cartitem_product_id AND cartitem_deleted IS NULL ';
        $params = array(
                    'cartitem_cart_id' => $this->cart_id,
                    'cartitem_product_id' => $productId,
        );
        $params2 = array(
                ':cartitem_product_name' => $ProductDescription,
                ':cartitem_custom_type' => $paytypeName,
                ':cartitem_product_price' => $price,
                ':cartitem_quantity' => 1,
                ':cartitem_subtotal' => $price,
                ':cartitem_product_gst' => 1,
                ':cartitem_user_ip' => $_SERVER['REMOTE_ADDR'],
                ':cartitem_user_browser' => $_SERVER['HTTP_USER_AGENT'],
        );
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $params2 = array_merge($params2, array(':cartitem_id' => $res[0]['cartitem_id']));
            $sql = 'UPDATE tbl_cartitem SET cartitem_product_name = :cartitem_product_name, cartitem_custom_type = :cartitem_custom_type, cartitem_product_price = :cartitem_product_price, cartitem_quantity = :cartitem_quantity, cartitem_subtotal = :cartitem_subtotal, cartitem_product_gst = :cartitem_product_gst, cartitem_user_ip = :cartitem_user_ip, cartitem_user_browser = :cartitem_user_browser, cartitem_modified = now()
            WHERE cartitem_id = :cartitem_id';
        } else {
            $params2 = array_merge($params2, $params);
            $sql = 'INSERT INTO tbl_cartitem (
                cartitem_cart_id,
                cartitem_product_id,
                cartitem_product_name,
                cartitem_custom_type,
                cartitem_product_price,
                cartitem_quantity,
                cartitem_subtotal,
                cartitem_product_gst,
                cartitem_user_ip,
                cartitem_user_browser,
                cartitem_created) VALUES (
                :cartitem_cart_id, 
                :cartitem_product_id,
                :cartitem_product_name,
                :cartitem_custom_type,
                :cartitem_product_price,
                :cartitem_quantity,
                :cartitem_subtotal,
                :cartitem_product_gst,
                :cartitem_user_ip,
                :cartitem_user_browser, now())';
        }
        $DBobject->wrappedSql($sql, $params2);
        if ($cartitem_id = $DBobject->wrappedSqlIdentity()) {
            foreach ($attributes as $key => $val) {
                $params = array(
                        ':cid' => $cartitem_id,
                        ':attribute_name' => $key,
                        ':attr_value_name' => $val,
                );
                $sql = 'INSERT INTO tbl_cartitem_attr ( cartitem_attr_cartitem_id, cartitem_attr_attribute_name, cartitem_attr_attr_value_name, cartitem_attr_created )
                    VALUES( :cid, :attribute_name, :attr_value_name, now() )';
                $res2 = $DBobject->wrappedSql($sql, $params);
            }

            return "'{$ProductDescription}' 已经添加到购物车。";
        }

        return "错误: '{$ProductDescription}' 不能添加到购物车。";
    }

    /**
     * Return array with product details on current cart (or given cartId)
     * Include tbl_cartitem, tbl_cartitem_attr and product image gallery.
     *
     * @return array
     */
    public function getDataProductsOnCart($cartId = null)
    {
        global $DBobject;

        if (is_null($cartId)) {
            $cartId = $this->cart_id;
        }

        $cart_arr = array();
        $classTermObj = new ClassTermClass();

        // Get cart items: customs and classes only - ignore merchandise
        $sql = "SELECT * FROM tbl_cartitem 
        WHERE cartitem_cart_id = :id AND cartitem_deleted IS NULL AND cartitem_cart_id <> '0' AND (cartitem_class_flag = '1' OR cartitem_product_id < 0)";
        $res = $DBobject->wrappedSql($sql, [':id' => $cartId]);
        foreach ($res as $p) {
            $cart_arr[$p['cartitem_id']] = $p;

            // Get more details for class items
            if ($p['cartitem_class_flag'] == 1) {
                $cart_arr[$p['cartitem_id']] = array_merge($cart_arr[$p['cartitem_id']], $classTermObj->GetClassTermData($p['cartitem_product_id'], false));
            }

            // -- ATTRIBUTES SAVED IN tbl_cartitem_attr -------
            $sql = "SELECT cartitem_attr_attribute_name, cartitem_attr_attr_value_name 
            FROM tbl_cartitem_attr
            WHERE cartitem_attr_cartitem_id = :id 
            AND cartitem_attr_deleted IS NULL 
            AND cartitem_attr_cartitem_id <> '0'";

            $res2 = $DBobject->wrappedSql($sql, array(
                    ':id' => $p['cartitem_id'],
            ));
            $cart_arr[$p['cartitem_id']]['attributes'] = $res2;
        }

        return array_merge($cart_arr, parent::GetDataProductsOnCart($cartId));
    }

    /**
     * Validate all items on current cart and totals and return messages in array.
     *
     * @return array
     */
    public function validateCart($backend = false)
    {
        global $DBobject;

        $message = parent::ValidateCart();
        $classTermObj = new ClassTermClass();

        $sql = "SELECT * FROM tbl_cartitem 
        WHERE cartitem_deleted is null 
        AND cartitem_cart_id = :id AND cartitem_class_flag = '1'";
        if ($res = $DBobject->wrappedSql($sql, [':id' => $this->cart_id])) {
            foreach ($res as $item) {
                $classData = $classTermObj->GetClassTermData($item['cartitem_product_id'], $backend);
                //var_dump($classData);exit;
                $classTermName = "{$classData['class_name']} | {$classData['term_name']}";

                if (strtotime($classData['term_end_date']) < strtotime(date('Y-m-d'))) {
                    $message[] = "课程 “{$item['cartitem_product_name']}” 不可以报名。学期已经结束。";
                    $this->RemoveFromCart($item['cartitem_id']);

                    return $message;
                }
                if (empty($classData['class_id']) || empty($classData['term_id'])) {
                    $message[] = "The class '{$item['cartitem_product_name']}' is no longer available. ";
                    $this->RemoveFromCart($item['cartitem_id']);

                    return $message;
                }
                if (!$backend && ($classData['availability']['available'] < 1)) {
                    $message[] = "课程 “$classTermName” 已经报满。";
                    $this->RemoveFromCart($item['cartitem_id']);

                    return $message;
                }

                $newQty = 0;
                $newQtySql = '';
                if (!$backend && ($classData['availability']['available'] < $item['cartitem_quantity'])) {
                    $newQty = $classData['availability']['available'];
                    $item['cartitem_quantity'] = $classData['availability']['available'];
                    $newQtySql = ', cartitem_quantity = :qty';
                    $spots = ($newQty > 1) ? '1 spot' : "$newQty spots";
                    $message[] = "课程 “$classTermName” 只有 $spots 可以报名。";
                }

                $sessions = $classData['availability']['weeks'];
                $classPrice = count($sessions) * count($classData['classtimes']) * ((floatval($classData['class_special_price']) > 0) ? floatval($classData['class_special_price']) : floatval($classData['class_price']));

                if ($item['cartitem_enrolment_type'] == 'T') {
                    $classTermName = '体验课 - '.$classTermName;
                    $classPrice = 0;
                }

                if ($classPrice != $item['cartitem_product_price'] || $classTermName != $item['cartitem_product_name'] || $newQty > 0) {
                    $sql = "UPDATE tbl_cartitem SET cartitem_product_price = :price, cartitem_product_name = :product_name, cartitem_subtotal = :subtotal $newQtySql WHERE cartitem_id = :id";
                    $params = array(
                            ':id' => $item['cartitem_id'],
                            ':price' => $classPrice,
                            ':product_name' => $classTermName,
                            ':subtotal' => $classPrice * $item['cartitem_quantity'],
                    );
                    if ($newQty > 0 && !empty($newQtySql)) {
                        $params = array_merge($params, array(':qty' => $newQty));
                    }
                    $DBobject->wrappedSql($sql, $params);

                    //UPDATE THE SESSIONS/WEEKS
                    if ($item['cartitem_enrolment_type'] != 'T') {
                        $sql = 'UPDATE tbl_cartitem_attr SET cartitem_attr_deleted = NOW() 
                        WHERE cartitem_attr_cartitem_id = :cartitem_id 
                        AND cartitem_attr_deleted IS NULL';
                        $DBobject->wrappedSql($sql, array('cartitem_id' => $item['cartitem_id']));
                        foreach ($sessions as $attr) {
                            $params = [
                                ':cid' => $item['cartitem_id'],
                                ':attribute_name' => 'week',
                                ':attr_value_name' => $attr,
                            ];
                            $sql = 'INSERT INTO tbl_cartitem_attr (
                                cartitem_attr_cartitem_id,
                                cartitem_attr_attribute_name,
                                cartitem_attr_attr_value_name,
                                cartitem_attr_created
                                ) VALUES (
                                :cid,
                                :attribute_name,
                                :attr_value_name,
                                now()
                            )';
                            $DBobject->wrappedSql($sql, $params);
                        }
                    }

                    if ($classPrice != $item['cartitem_product_price']) {
                        $message[] = "课程 “$classTermName” 的价格已经更新。";
                    }
                }
            }
        }
        return $message;
    }

    /**
     * Update product items quantities on cart.
     *
     * @param array $qtys
     * @param bool  $backend
     *
     * @return array
     */
    public function updateQtyCart($qtys, $backend = false)
    {
        global $DBobject;

        $result = array();
        $productQtys = array();
        foreach ($qtys as $id => $qty) {
            $sql = "SELECT cartitem_quantity, cartitem_product_price, cartitem_product_id,cartitem_product_name FROM tbl_cartitem WHERE cartitem_id = :id AND cartitem_deleted IS NULL AND cartitem_class_flag = '1'";
            if ($res = $DBobject->wrappedSql($sql, array(
                    ':id' => $id,
            ))) {
                if ($qty != $res[0]['cartitem_quantity']) {
                    $classTermObj = new ClassTermClass();
                    $classData = $classTermObj->GetClassTermData($res[0]['cartitem_product_id'], false);
                    if ($qty > $classData['availability']['available'] && !$backend) {
                        $result['subtotals'][$id] = $res[0]['cartitem_subtotal'];
                        $result['pricemodifier'][$id] = '';
                        $result['priceunits'][$id] = $res[0]['cartitem_product_price'];
                        $result['error'][$id] = 'There are not enough available spots for '.$res[0]['cartitem_product_name'].'. ';
                        continue;
                    }

                    $subtotal = $res[0]['cartitem_product_price'] * $qty;
                    $params = array(
                            ':id' => $id,
                            ':qty' => $qty,
                            ':subtotal' => $subtotal,
                    );
                    $sql = 'UPDATE tbl_cartitem SET cartitem_quantity = :qty, cartitem_subtotal = :subtotal, cartitem_modified = now() WHERE cartitem_id = :id';
                    if ($DBobject->wrappedSql($sql, $params)) {
                        $result['subtotals'][$id] = $subtotal;
                        $result['pricemodifier'][$id] = '';
                        $result['priceunits'][$id] = $res[0]['cartitem_product_price'];
                    }
                }
            } else { // IS NOT CLASS - IS PRODUCT
                $productQtys["$id"] = $qty;
            }
        }
        if (!empty($productQtys)) {
            $result = array_merge($result, parent::UpdateQtyCart($productQtys));
        }

        return $result;
    }

    public function addEnrolment($_studentId, $_termObjId, $_classObjId, $_week, $_type, $_paymentId = 0)
    {
        global $DBobject;

        $sql = 'INSERT INTO tbl_enrolment (enrolment_student_id, enrolment_term_object_id, enrolment_class_object_id, enrolment_week, enrolment_type, enrolment_payment_id, enrolment_created ) 
                VALUES (:enrolment_student_id, :enrolment_term_object_id, :enrolment_class_object_id, :enrolment_week, :enrolment_type, :enrolment_payment_id, now() )';
        $params = array(
                ':enrolment_student_id' => $_studentId,
                ':enrolment_term_object_id' => $_termObjId,
                ':enrolment_class_object_id' => $_classObjId,
                ':enrolment_week' => $_week,
                ':enrolment_type' => $_type,
                ':enrolment_payment_id' => $_paymentId,
        );
        $DBobject->wrappedSql($sql, $params);

        return $DBobject->wrappedSqlIdentity();
    }

    /**
     * Add enrolment record given the classterm_id.
     *
     * @param int $_studentId
     * @param int $_classtermId
     * @param int $_week
     * @param int $_type
     * @param int $_paymentId
     *
     * @return int or false
     */
    public function addEnrolmentByClasstermId($_studentId, $_classTermId, $_week, $_type, $_active, $_paymentId = 0)
    {
        global $DBobject;

        $sql = "SELECT tbl_classterm.*, term_object_id, class_object_id FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id  
              WHERE classterm_deleted IS NULL AND classterm_published = '1' AND classterm_id = :id AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1'";
        $params = array(':id' => $_classTermId);
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $params = array(
                ':enrolment_student_id' => $_studentId,
                ':enrolment_term_object_id' => $res[0]['term_object_id'],
                ':enrolment_class_object_id' => $res[0]['class_object_id'],
                ':enrolment_week' => $_week,
                ':enrolment_type' => $_type,
                ':enrolment_published' => $_active,
                ':enrolment_payment_id' => $_paymentId,
                );

                //CHECK FOR DUPLICATE
                $sql = 'SELECT enrolment_id FROM tbl_enrolment WHERE enrolment_student_id = :enrolment_student_id AND enrolment_term_object_id = :enrolment_term_object_id AND 
                  enrolment_class_object_id = :enrolment_class_object_id AND enrolment_week = :enrolment_week AND enrolment_type = :enrolment_type AND enrolment_published = :enrolment_published
                  AND enrolment_payment_id = :enrolment_payment_id';
            if ($enrol = $DBobject->wrappedSql($sql, $params)) {
                return $enrol[0]['enrolment_id'];
            }

            $sql = 'INSERT INTO tbl_enrolment (enrolment_student_id, enrolment_term_object_id, enrolment_class_object_id, enrolment_week, enrolment_type, enrolment_published, enrolment_payment_id, enrolment_created )
                    VALUES (:enrolment_student_id, :enrolment_term_object_id, :enrolment_class_object_id, :enrolment_week, :enrolment_type, :enrolment_published, :enrolment_payment_id, now() )';
            $DBobject->wrappedSql($sql, $params);
            $enrolmentId = $DBobject->wrappedSqlIdentity();

                // Unpublish trial
            if (!empty($enrolmentId) && $_type == 'S') {
                $sql = "UPDATE tbl_enrolment SET enrolment_published = '0', enrolment_modified = now() WHERE enrolment_deleted IS NULL AND enrolment_type = 'T' AND enrolment_published = '1' 
                        AND enrolment_student_id = :enrolment_student_id AND enrolment_term_object_id = :enrolment_term_object_id ";
                $params = array(
                    ':enrolment_student_id' => $_studentId,
                    ':enrolment_term_object_id' => $res[0]['term_object_id'], );
                $DBobject->wrappedSql($sql, $params);
            }

            return $enrolmentId;
        }

        return false;
    }

    /**
     * Delete an enrolment record given the enrolment_id.
     *
     * @param int $_enrolmentId
     *
     * @return bool
     */
    public function deleteEnrolmentById($_enrolmentId)
    {
        global $DBobject;

        $sql = 'UPDATE tbl_enrolment SET enrolment_deleted = now() WHERE enrolment_id = :enrolment_id';
        $params = array(':enrolment_id' => $_enrolmentId);
        $DBobject->wrappedSql($sql, $params);

        return true;
    }

    /**
     * Unpublish an enrolment record given the enrolment_id.
     *
     * @param int $_enrolmentId
     *
     * @return bool
     */
    public function unpublishEnrolmentById($_enrolmentId)
    {
        global $DBobject;

        $sql = 'UPDATE tbl_enrolment SET enrolment_published = 0, enrolment_modified = now() WHERE enrolment_id = :enrolment_id';
        $params = array(':enrolment_id' => $_enrolmentId);
        $DBobject->wrappedSql($sql, $params);

        return true;
    }

    /**
     * Return enrolment recordset given the enrolment_id.
     * Optional: $_fieldsArr must have two associative arrays "params" and "sql"
     * ie: array("params"=>array(":enrolment_student_id"=> 8), "sql"=>array('enrolment_student_id = :enrolment_student_id') ).
     *
     * @param unknown $_enrolmentId
     * @param unknown $_fieldsArr
     */
    public function getEnrolment($_enrolmentId, $_fieldsArr = array())
    {
        global $DBobject;

        $params = array(':enrolment_id' => $_enrolmentId);
        $fieldsStr = '';
        if (!empty($_fieldsArr)) {
            $params = array_merge($params, $_fieldsArr['params']);
            $fieldsStr = 'AND '.implode(' AND ', $_fieldsArr['sql']);
        }
        $sql = "SELECT * FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_id = :enrolment_id $fieldsStr";

        return $DBobject->wrappedSql($sql, $params);
    }

    /**
     * Set enrolment attendance given the enrolment_id and attendance value.
     *
     * @param int  $_enrolmentId
     * @param bool $_attended
     *
     * @return bool
     */
    public function setEnrolmentAttendanceById($_enrolmentId, $_attended)
    {
        global $DBobject;

        $sql = 'UPDATE tbl_enrolment SET enrolment_attended = :enrolment_attended, enrolment_modified = now()     WHERE enrolment_id = :enrolment_id';
        $params = array(':enrolment_id' => $_enrolmentId, ':enrolment_attended' => $_attended);
        $DBobject->wrappedSql($sql, $params);

        return true;
    }

    /**
     * Return enrolment type full name give the type_code.
     *
     * @param string $_typeCode
     *
     * @return string
     */
    public function getType($_typeCode)
    {
        global $DBobject;

        $params = array(':type_code' => $_typeCode);
        $sql = 'SELECT type_name FROM tbl_type WHERE type_deleted IS NULL AND type_code = :type_code';
        $res = $DBobject->wrappedSql($sql, $params);

        return $res[0]['type_name'];
    }

    /**
     * Return student enrolment records given the student_id.
     * Optional: $_type for type of enrolment. Default null to get all.
     * Optional: $_onlyActive filter. Default false.
     *
     * @param int    $_studentId
     * @param string $_type
     * @param bool   $_onlyActive
     *
     * @return array
     */
    public function getStudentEnrolments($_studentId, $_type = '', $_onlyActive = false, $_termObjId = 0)
    {
        global $DBobject;

        $result = array();
        $classterm_obj = new ClassTermClass();

        //WAITLING LIST
        $params = array(':waitlist_student_id' => $_studentId);
        $termSql = '';
        if (!empty($_termObjId)) {
            $termSql = 'AND waitlist_term_object_id = :waitlist_term_object_id';
            $params = array_merge($params, array(':waitlist_term_object_id' => $_termObjId));
        }
        $sql = "SELECT * FROM tbl_waitlist WHERE waitlist_student_id = :waitlist_student_id AND waitlist_deleted IS NULL $termSql ORDER BY waitlist_created DESC, waitlist_modified DESC";
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            foreach ($res as $st) {
                $term = $classterm_obj->GetTermDataByObjId($st['waitlist_term_object_id']);
                $class = $classterm_obj->getClassDataByObjId($st['waitlist_class_object_id']);
                $result['classterms'][] = array_merge(
                    array_merge($term, $class),
                    array(
                                'enrolment_type' => 'W',
                                'enrolment_week' => $st['waitlist_id'],
                                'enrolment_type_name' => $this->GetType('W'),
                                'enrolment_type_name_date' => $this->GetType('W'),
                                'enrolment_published' => 1,
                                'reenrol' => '',
                                'waitlist_id' => $st['waitlist_id'],
                        )
                );
            }
        }

        //ENROLMENTS LIST
        $params = array(':enrolment_student_id' => $_studentId);
        $active = '';
        if ($_onlyActive) {
            $active = 'AND enrolment_published = :enrolment_published';
            $params = array_merge($params, array(':enrolment_published' => 1));
        }
        $type = '';
        if (!empty($_type)) {
            $type = 'AND enrolment_type = :enrolment_type';
            $params = array_merge($params, array(':enrolment_type' => $_type));
        }
        $termSql = '';
        if (!empty($_termObjId)) {
            $termSql = 'AND enrolment_term_object_id = :enrolment_term_object_id';
            $params = array_merge($params, array(':enrolment_term_object_id' => $_termObjId));
        }

        $sql = "SELECT * FROM tbl_enrolment WHERE enrolment_student_id = :enrolment_student_id AND enrolment_deleted IS NULL $type $active $termSql ORDER BY enrolment_term_object_id DESC, enrolment_class_object_id DESC, enrolment_published DESC, enrolment_modified DESC";
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $result['enrolments'] = $res;

            $sql = "SELECT DISTINCT enrolment_term_object_id, enrolment_class_object_id, enrolment_type, enrolment_week, enrolment_published, enrolment_modified FROM tbl_enrolment WHERE enrolment_student_id = :enrolment_student_id AND enrolment_deleted IS NULL $type $active $termSql ORDER BY enrolment_term_object_id DESC, enrolment_class_object_id DESC, enrolment_published DESC, enrolment_modified DESC";
            if ($res = $DBobject->wrappedSql($sql, $params)) {
                $checkArr = array();
                foreach ($res as $st) {
                    $checkStr = $st['enrolment_term_object_id'].'-'.$st['enrolment_class_object_id'].'-'.$st['enrolment_type'];
                    if (in_array($checkStr, $checkArr) && $st['enrolment_type'] != 'MK' && $st['enrolment_type'] != 'T') {
                        continue;
                    }
                    $checkArr[] = $checkStr;
                    $term = $classterm_obj->GetTermDataByObjId($st['enrolment_term_object_id']);
                    $class = $classterm_obj->getClassDataByObjId($st['enrolment_class_object_id']);

                    if (empty($term) || empty($class)) {
                        continue;
                    }

                    //Check for Reenrol
                    $reenrol = $classterm_obj->GetReEnrolClassTermData($st['enrolment_term_object_id'], $st['enrolment_class_object_id']);
                    $sql = "SELECT enrolment_id FROM tbl_enrolment WHERE enrolment_published = '1' AND enrolment_deleted IS NULL AND (enrolment_type = 'MV' OR enrolment_type = 'S') AND enrolment_student_id = :enrolment_student_id AND enrolment_class_object_id = :enrolment_class_object_id AND enrolment_term_object_id = :enrolment_term_object_id";
                    if ($DBobject->wrappedSql($sql, array('enrolment_student_id' => $_studentId, 'enrolment_class_object_id' => $reenrol['class_object_id'], 'enrolment_term_object_id' => $reenrol['term_object_id']))) {
                        //student has already reenrolled
                        $reenrol = null;
                    }

                    $date = '';
                    if ($st['enrolment_type'] == 'MK' || $st['enrolment_type'] == 'T') {
                        $week = empty($st['enrolment_week']) ? 0 : intval($st['enrolment_week']) - 1;
                        //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') die(var_dump($class['class_weekday'].' - '.$term['term_start_date'].' - '. $week));
                        $date = ' ['.getDateByWeekdayStartDate($class['class_weekday'], $term['term_start_date'], $week, 'd/m/Y').']';
                    }

                    $result['classterms'][] = array_merge(
                        array_merge($term, $class),
                        array(
                                        'enrolment_type' => $st['enrolment_type'],
                                        'enrolment_week' => $st['enrolment_week'],
                                        'enrolment_type_name' => $this->GetType($st['enrolment_type']),
                                        'enrolment_type_name_date' => $this->GetType($st['enrolment_type']).$date,
                                        'enrolment_published' => $st['enrolment_published'],
                                        'reenrol' => $reenrol,
                                        'waitlist_id' => '',
                        )
                    );
                }
            }
        }

        return $result;
    }

    /**
     * Return classterm enrolment records given the term_object_id, class_object_id and week.
     * Optional: $_type for type of enrolment. Default null to get all.
     * Optional: $_onlyActive filter. Default false.
     *
     * @param int    $_termObjId
     * @param int    $_classObjId
     * @param int    $_week
     * @param string $_type
     * @param bool   $_onlyActive
     *
     * @return array
     */
    public function getClassTermEnrolments($_termObjId, $_classObjId, $_week, $_type = '', $_onlyActive = false)
    {
        global $DBobject;

        $params = array(':enrolment_term_object_id' => $_termObjId, ':enrolment_class_object_id' => $_classObjId, ':enrolment_week' => $_week);
        $active = '';
        if ($_onlyActive) {
            $active = 'AND enrolment_published = :enrolment_published';
            $params = array_merge($params, array(':enrolment_published' => 1));
        }
        $type = '';
        if (!empty($_type)) {
            $type = 'AND enrolment_type = :enrolment_type';
            $params = array_merge($params, array(':enrolment_type' => $_type));
        }

        $sql = "SELECT tbl_enrolment.*, tbl_student.*, tbl_type.type_name FROM tbl_enrolment LEFT JOIN tbl_student ON student_id = enrolment_student_id LEFT JOIN tbl_type ON type_code = enrolment_type
                WHERE enrolment_deleted IS NULL AND student_deleted IS NULL AND type_deleted IS NULL
                AND enrolment_term_object_id = :enrolment_term_object_id AND enrolment_class_object_id = :enrolment_class_object_id
                AND enrolment_week = :enrolment_week  $type $active ORDER BY student_name, student_surname";

        return $DBobject->wrappedSql($sql, $params);
    }

    /**
     * Convert Trial to standard class. Just add a classterm to cart.
     *
     * @param int $_studentId
     * @param int $_classObjId
     * @param int $_termObjId
     *
     * @return array
     */
    public function convertStudents($_studentId, $_classObjId, $_termObjId)
    {
        global $DBobject;

        $classTerm_obj = new ClassTermClass();
        $result = array();
        if ($classtermArr = $classTerm_obj->GetClassTermDataByObjIds($_termObjId, $_classObjId, true)) {
            $AttributesArr = array('week' => $classtermArr['availability']['weeks']);
            $this->AddToCart($classtermArr['classterm_id'], $AttributesArr, 1, 99, null, 0, true, 'S', true);
            $result['selectedStudent']["{$_studentId}"]["{$classtermArr['classterm_id']}"] = true;
        }

        return $result;
    }

    /**
     * Set enrolment_published to zero given the student_id, term_object_id, class_object_id and enrolment_type.
     *
     * @param int    $_studentId
     * @param int    $_termObjId
     * @param int    $_ClassObjId
     * @param string $_type
     *
     * @return bool
     */
    public function unpublishEnrolments($_studentId, $_termObjId, $_ClassObjId, $_type, $_week = '')
    {
        global $DBobject;

        $weekStr = '';
        if (($_type == 'MK' || $_type == 'T') && !empty($_week)) {
            $weekStr = " AND enrolment_week = '$_week'";
        }
        $sql = "UPDATE tbl_enrolment SET enrolment_published = '0', enrolment_modified = now()    WHERE enrolment_deleted IS NULL AND enrolment_student_id = :enrolment_student_id AND enrolment_term_object_id = :enrolment_term_object_id
                            AND enrolment_class_object_id = :enrolment_class_object_id AND enrolment_type = :enrolment_type {$weekStr}";
        $params = array(':enrolment_student_id' => $_studentId, ':enrolment_term_object_id' => $_termObjId, ':enrolment_class_object_id' => $_ClassObjId, ':enrolment_type' => $_type);
        $DBobject->wrappedSql($sql, $params);

        return true;
    }

    /**
     * Set enrolment_published given the payment_id.
     *
     * @param int $_studentId
     *
     * @return bool
     */
    public function setEnrolmentPublishByPaymentId($_paymentId, $_status)
    {
        global $DBobject;

        $sql = 'UPDATE tbl_enrolment SET enrolment_published = :enrolment_published, enrolment_modified = now()    WHERE enrolment_deleted IS NULL AND enrolment_payment_id = :enrolment_payment_id';
        $params = array(':enrolment_payment_id' => $_paymentId, ':enrolment_published' => $_status);
        $DBobject->wrappedSql($sql, $params);

        return true;
    }

    /**
     * Delete enrolments given the $_paymentId.
     *
     * @param tinyint $_published
     * @param int     $_paymentId
     *
     * @return bool
     */
    public function deleteEnrolmentByPaymentId($_paymentId)
    {
        global $DBobject;

        $sql = 'UPDATE tbl_enrolment SET enrolment_deleted = now() WHERE enrolment_deleted IS NULL AND enrolment_payment_id = :enrolment_payment_id';
        $params = array(':enrolment_payment_id' => $_paymentId);
        $DBobject->wrappedSql($sql, $params);

        return true;
    }

    /**
     * Delete student's enrolment records given the student_id.
     * Optional: $_type for type of enrolment. Default null for all.
     * Optional: $_onlyActive filter. Default false.
     *
     * @param int    $_studentId
     * @param string $_type
     * @param bool   $_onlyActive
     *
     * @return array
     */
    public function deleteStudentEnrolments($_studentId, $_type = '', $_onlyActive = false)
    {
        global $DBobject;

        $params = array(':enrolment_student_id' => $_studentId);
        $active = '';
        if ($_onlyActive) {
            $active = 'AND enrolment_published = :enrolment_published';
            $params = array_merge($params, array(':enrolment_published' => 1));
        }
        $type = '';
        if (!empty($_type)) {
            $type = 'AND enrolment_type = :enrolment_type';
            $params = array_merge($params, array(':enrolment_type' => $_type));
        }

        $sql = "UPDATE tbl_enrolment SET enrolment_deleted = now() WHERE enrolment_student_id = :enrolment_student_id AND enrolment_deleted IS NULL $type $active ";

        return $DBobject->wrappedSql($sql, $params);
    }

    /**
     * Move current/existing enrolment to a new classterm. It deletes the existing and add new enrolments.
     *
     * @param int $_StudentId
     * @param int $_OldTermObjId
     * @param int $_OldClassObjId
     * @param int $_NewTermObjId
     * @param int $_NewClassObjId
     *
     * @return bool
     */
    public function moveEnrolment($_StudentId, $_OldTermObjId, $_OldClassObjId, $_newClassTermId)
    {
        global $DBobject;

        $classTerm_obj = new ClassTermClass();
        $termData = $classTerm_obj->GetTermDataByObjId($_OldTermObjId);
        $curWeek = $classTerm_obj->GetCurrentWeekByTermStartDate($termData['term_start_date']);
        $params = array(
                ':enrolment_student_id' => $_StudentId,
                ':enrolment_term_object_id' => $_OldTermObjId,
                ':enrolment_class_object_id' => $_OldClassObjId,
        );
        $sql = "SELECT enrolment_id, enrolment_payment_id, enrolment_week FROM tbl_enrolment WHERE enrolment_student_id = :enrolment_student_id AND enrolment_term_object_id = :enrolment_term_object_id AND enrolment_class_object_id = :enrolment_class_object_id AND enrolment_deleted IS NULL AND enrolment_published = '1' ";
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            foreach ($res as $r) {
                $this->UnpublishEnrolmentById($r['enrolment_id']);
                if ($r['enrolment_week'] >= $curWeek) {
                    $result = $this->AddEnrolmentByClasstermId($_StudentId, $_newClassTermId, $r['enrolment_week'], 'MV', 1, $r['enrolment_payment_id']);
                    if (empty($result)) {
                        return false;
                    }
                }
            }

            return true;
        }

        return false;
    }

    /**
     * Add Make-up enrolment only when the given student has enrolment (given the term & class) for the current week.
     *
     * @param int $_StudentId
     * @param int $_OldTermObjId
     * @param int $_OldClassObjId
     * @param int $_NewTermObjId
     * @param int $_NewClassObjId
     * @param int $_week
     *
     * @return bool
     */
    public function addMakeUpEnrolment($_StudentId, $_OldTermObjId, $_OldClassObjId, $_newClassTermId, $_week)
    {
        global $DBobject;

        $classTerm_obj = new ClassTermClass();
        $termData = $classTerm_obj->GetTermDataByObjId($_OldTermObjId);
        $params = array(
                ':enrolment_student_id' => $_StudentId,
                ':enrolment_term_object_id' => $_OldTermObjId,
                ':enrolment_class_object_id' => $_OldClassObjId,
        );
        //Check duplicated
        $sql = "SELECT enrolment_id FROM tbl_enrolment LEFT JOIN tbl_term ON term_object_id = enrolment_term_object_id LEFT JOIN tbl_classterm ON classterm_term_id = term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id
        WHERE enrolment_deleted IS NULL AND enrolment_published = 1 AND (enrolment_type = 'S' OR enrolment_type = 'MV' OR enrolment_type = 'MK')  
            AND classterm_deleted IS NULL AND classterm_published = 1 AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1'
            AND class_object_id = enrolment_class_object_id AND enrolment_student_id = :enrolment_student_id AND classterm_id = :classterm_id AND enrolment_week = :enrolment_week";
        if ($res = $DBobject->wrappedSql($sql, array(':enrolment_student_id' => $_StudentId, ':classterm_id' => $_newClassTermId, ':enrolment_week' => $_week))) {
            return false;
        }
        //Get old payment_id and insert Make-up
        $sql = "SELECT enrolment_id, enrolment_payment_id FROM tbl_enrolment WHERE enrolment_student_id = :enrolment_student_id AND enrolment_term_object_id = :enrolment_term_object_id AND enrolment_class_object_id = :enrolment_class_object_id AND enrolment_deleted IS NULL AND enrolment_published = '1' AND (enrolment_type = 'S' OR enrolment_type = 'MV') ";
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $result = $this->AddEnrolmentByClasstermId($_StudentId, $_newClassTermId, $_week, 'MK', 1, $res[0]['enrolment_payment_id']);
            if (empty($result)) {
                return false;
            }

            return true;
        }

        return false;
    }

    /**
     * Move trial enrolment only when the given student has enrolment (given the term & class) for the current week.
     *
     * @param int $_StudentId
     * @param int $_OldTermObjId
     * @param int $_OldClassObjId
     * @param int $_NewTermObjId
     * @param int $_NewClassObjId
     * @param int $_week
     *
     * @return bool
     */
    public function moveTrialEnrolment($_StudentId, $_OldTermObjId, $_OldClassObjId, $_newClassTermId, $_week)
    {
        global $DBobject;

        $classTerm_obj = new ClassTermClass();
        $termData = $classTerm_obj->GetTermDataByObjId($_OldTermObjId);
        $params = array(
                ':enrolment_student_id' => $_StudentId,
                ':enrolment_term_object_id' => $_OldTermObjId,
                ':enrolment_class_object_id' => $_OldClassObjId,
        );
        $sql = "SELECT enrolment_id, enrolment_payment_id FROM tbl_enrolment WHERE enrolment_student_id = :enrolment_student_id AND enrolment_term_object_id = :enrolment_term_object_id AND enrolment_class_object_id = :enrolment_class_object_id AND enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_type = 'T' ";
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $this->UnpublishEnrolmentById($res[0]['enrolment_id']);
            $result = $this->AddEnrolmentByClasstermId($_StudentId, $_newClassTermId, $_week, 'T', 1, $res[0]['enrolment_payment_id']);
            if (empty($result)) {
                return false;
            }

            return true;
        }

        return false;
    }
    /**
     * Calculate total and return all amounts of the current cart - Including siblings discount.
     *
     * @return array
     */
    public function calculateTotal($_studentClass = array())
    {
        global $DBobject;

        $result = parent::CalculateTotal();
        $discount = $this->GetSiblingDiscount($_studentClass);
        $result['discount'] = floatval($result['discount']) + $discount;
        $result['GST_Taxable'] = $result['GST_Taxable'] - $discount;
        $result['total'] = $result['total'] - $discount;

        return $result;
    }

    /**
     * Reenrol students from the last term given the user_id. Just add a classterm to cart.
     *
     * @param int $_userId
     * @param int $_studentId
     * @param int $_classObjId
     *
     * @return bool
     */
    public function reenrolStudents($_userId, $_studentId, $_classObjId)
    {
        global $DBobject;

        $result = array();
        $member_obj = new Member();
        $addressArr = $member_obj->GetUsersAddresses($_userId);

        $classTerm_obj = new ClassTermClass();
        $CurrentTerms = $classTerm_obj->GetCurrentTerm(0, $addressArr[0]['address_postcode']);
        if (count($CurrentTerms) >= 1) {
            $newTerm = end($CurrentTerms);
            $termObjId = $newTerm['term_object_id'];
            if ($classtermArr = $classTerm_obj->GetClassTermDataByObjIds($termObjId, $_classObjId, true)) {
                $AttributesArr = array('week' => $classtermArr['availability']['weeks']);

                //Check for existing classterm in the shopping cart then add it
                $sql = "SELECT cartitem_id FROM tbl_cartitem
                WHERE cartitem_cart_id = :id AND cartitem_deleted IS NULL AND cartitem_cart_id <> '0' AND cartitem_product_id = :cartitem_product_id";
                $res = $DBobject->wrappedSql($sql, array(':id' => $this->cart_id, ':cartitem_product_id' => $classtermArr['classterm_id']));
                if (empty($res)) {
                    $this->AddToCart($classtermArr['classterm_id'], $AttributesArr, 1, 99, null, 0, true, 'S', true);
                }

                $result['selectedStudent']["{$_studentId}"]["{$classtermArr['classterm_id']}"] = true;
            }
        }

        return $result;
    }

    /**
     * Calculate sibling discount for the current transaction.
     *
     * @param array $_studentClass
     *
     * @return number
     */
    public function getSiblingDiscount($_studentClass = array())
    {
        global $DBobject;
        $discount = 0;
        if (!empty($_studentClass)) {
            $studentClass = array();
            $tmpInt = 1;
            foreach ($_studentClass as $key => $student) { // ASSIGN TEMPORARY STUDENT ID
                $studentClass[$key] = $student;
                if (empty($student['student_id'])) {
                    $studentClass[$key]['student_id'] = "T$tmpInt";
                    ++$tmpInt;
                }
            }
            $cartData = $this->GetDataCart();
            $sql = "SELECT cartitem_id, cartitem_product_price, cartitem_quantity, term_object_id  FROM tbl_cartitem LEFT JOIN tbl_classterm ON classterm_id = cartitem_product_id LEFT JOIN tbl_term ON term_id = classterm_term_id
                     WHERE cartitem_cart_id = :id AND cartitem_deleted IS NULL AND cartitem_cart_id <> '0' AND cartitem_class_flag = '1'";
            if ($res = $DBobject->wrappedSql($sql, array(':id' => $cartData['cart_id']))) {
                $termObjIds = array();
                $enrolIds = array();
                foreach ($res as $r) {
                    // CALCULATE DISCOUNT PER CARTITEM AND STUDENT
                    foreach ($studentClass as $student) {
                        if (!empty($student['cartitem_id'][$r['cartitem_id']])) {
                            //DON'T APPLY DISCOUNT FOR FIRST STUDENT
                            if (!empty($enrolIds["{$r['term_object_id']}"]) && !in_array($student['student_id'], $enrolIds["{$r['term_object_id']}"])) {
                                $discount = $discount + ($r['cartitem_product_price'] * 0.1);
                            }
                            $enrolIds["{$r['term_object_id']}"][] = $student['student_id'];
                        }
                    }
                }
            }
        }

        return round($discount, 2);
    }

    public function getSiblingDiscountNOTsameTransaction($_studentClass = array())
    {
        // ============> THIS FUNCTION IS NOT BEING USED <================
        global $DBobject;

        $discount = 0;
        if (!empty($_studentClass)) {
            $studentClass = array();
            $tmpInt = 1;
            foreach ($_studentClass as $key => $student) { // ASSIGN TEMPORARY STUDENT ID
                $studentClass[$key] = $student;
                if (empty($student['student_id'])) {
                    $studentClass[$key]['student_id'] = "T$tmpInt";
                    ++$tmpInt;
                }
            }

            $cartData = $this->GetDataCart();
            $sql = "SELECT cartitem_id, cartitem_product_price, cartitem_quantity, term_object_id  FROM tbl_cartitem LEFT JOIN tbl_classterm ON classterm_id = cartitem_product_id LEFT JOIN tbl_term ON term_id = classterm_term_id
                     WHERE cartitem_cart_id = :id AND cartitem_deleted IS NULL AND cartitem_cart_id <> '0' AND cartitem_class_flag = '1'";
            if ($res = $DBobject->wrappedSql($sql, array(':id' => $cartData['cart_id']))) {
                $termObjIds = array();
                $enrolIds = array();
                foreach ($res as $r) {
                    if (!in_array($r['term_object_id'], $termObjIds)) {  //BUILD ARRAY WITH ENROLLED STUDENTS IDS PER TERM
                        $termObjIds[] = $r['term_object_id'];
                        $sql = "SELECT DISTINCT student_id FROM tbl_student LEFT JOIN tbl_enrolment ON student_id = enrolment_student_id
                             WHERE student_user_id = :student_user_id AND enrolment_term_object_id = :enrolment_term_object_id AND student_deleted IS NULL AND enrolment_deleted IS NULL AND (enrolment_type = 'S' OR enrolment_type = 'MV') ";
                        $params = array(
                                ':student_user_id' => $cartData['cart_user_id'],
                                ':enrolment_term_object_id' => $r['term_object_id'],
                        );
                        if ($res2 = $DBobject->wrappedSql($sql, $params)) {
                            foreach ($res2 as $r2) {
                                $enrolIds["{$r['term_object_id']}"][] = $r2['student_id'];
                            }
                        }
                    }

                    // CALCULATE DISCOUNT PER CARTITEM AND STUDENT
                    foreach ($studentClass as $student) {
                        if (!empty($student['cartitem_id'][$r['cartitem_id']])) {
                            //DON'T APPLY DISCOUNT FOR FIRST STUDENT
                            if (!empty($enrolIds["{$r['term_object_id']}"]) && !in_array($student['student_id'], $enrolIds["{$r['term_object_id']}"])) {
                                $discount = $discount + ($r['cartitem_product_price'] * 0.1);
                            }
                            $enrolIds["{$r['term_object_id']}"][] = $student['student_id'];
                        }
                    }
                }
            }
        }

        return $discount;
    }

    /**
     * Return the number of classes on current cart (or given cartId).
     *
     * @return int
     */
    public function getQtyClassOnCart($cartId = null)
    {
        global $DBobject;

        if (is_null($cartId)) {
            $cartId = $this->cart_id;
        }
        $result = 0;
        $sql = "SELECT COUNT(cartitem_id) AS CNT FROM tbl_cartitem
                WHERE cartitem_cart_id = :id AND cartitem_deleted IS NULL AND cartitem_cart_id <> '0' AND cartitem_class_flag = '1'";
        if ($res = $DBobject->wrappedSql($sql, array(':id' => $cartId))) {
            $result = $res[0]['CNT'];
        }

        return $result;
    }

    /**
     * Check whether the student has enrolment given the student_id and classterm_id.
     *
     * @param int $_studentId
     * @param int $_classtermId
     *
     * @return bool
     */
    public function validateStudentEnrolment($_studentId, $_classtermId)
    {
        global $DBobject;

        $sql = "SELECT enrolment_id FROM tbl_enrolment LEFT JOIN tbl_term ON term_object_id = enrolment_term_object_id LEFT JOIN tbl_classterm ON classterm_term_id = term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id
        WHERE enrolment_deleted IS NULL AND enrolment_published = 1 AND (enrolment_type = 'S' OR enrolment_type = 'MV') AND enrolment_student_id = :enrolment_student_id 
            AND classterm_deleted IS NULL AND classterm_published = 1 AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1'
            AND class_object_id = enrolment_class_object_id AND classterm_id = :id";
        if ($res = $DBobject->wrappedSql($sql, array(':enrolment_student_id' => $_studentId, ':id' => $_classtermId))) {
            return false;
        }

        return true;
    }

    /**
     * Add the student to the class waiting list.
     *
     * @param int $_studentId
     * @param int $_termObjId
     * @param int $_classId
     *
     * @return bool
     */
    public function addStudentWaitList($_studentId, $_termObjId, $_classObjId)
    {
        global $DBobject;

        $params = array(':waitlist_student_id' => $_studentId, ':waitlist_term_object_id' => $_termObjId, ':waitlist_class_object_id' => $_classObjId);
        $sql = 'SELECT waitlist_id FROM tbl_waitlist WHERE waitlist_deleted IS NULL AND waitlist_student_id = :waitlist_student_id
                AND waitlist_term_object_id = :waitlist_term_object_id AND waitlist_class_object_id = :waitlist_class_object_id';
        $res = $DBobject->wrappedSql($sql, $params);
        if (empty($res)) {
            $sql = 'INSERT INTO tbl_waitlist ( waitlist_student_id, waitlist_term_object_id, waitlist_class_object_id, waitlist_created)
                        VALUES( :waitlist_student_id, :waitlist_term_object_id, :waitlist_class_object_id, now() )';
            if ($DBobject->wrappedSql($sql, $params)) {
                return true;
            }
        }

        return false;
    }

    /**
     * Return the wait list id given the student, term and class.
     *
     * @param int $_studentId
     * @param int $_termObjId
     * @param int $_classId
     *
     * @return bool
     */
    public function getStudentWaitListId($_studentId, $_termObjId, $_classObjId)
    {
        global $DBobject;

        $params = array(':waitlist_student_id' => $_studentId, ':waitlist_term_object_id' => $_termObjId, ':waitlist_class_object_id' => $_classObjId);
        $sql = 'SELECT waitlist_id FROM tbl_waitlist WHERE waitlist_deleted IS NULL AND waitlist_student_id = :waitlist_student_id
                AND waitlist_term_object_id = :waitlist_term_object_id AND waitlist_class_object_id = :waitlist_class_object_id';
        $res = $DBobject->wrappedSql($sql, $params);
        if (empty($res)) {
            return $res[0]['waitlist_id'];
        }

        return false;
    }

    /**
     * Remove the student from the class waiting list.
     *
     * @param int $_studentId
     * @param int $_termObjId
     * @param int $_classId
     *
     * @return bool
     */
    public function removeStudentWaitList($_waitlistId)
    {
        global $DBobject;

        $params = array(':waitlist_id' => $_waitlistId);
        $sql = 'UPDATE tbl_waitlist SET waitlist_deleted = now() WHERE waitlist_id = :waitlist_id';
        if ($DBobject->wrappedSql($sql, $params)) {
            return true;
        }

        return false;
    }

    /**
     * Get member's current debts.
     *
     * @param int $_userId
     *
     * @return bool
     */
    public function getMemberDebts($_userId)
    {
        global $DBobject;

        $params = array(':userId' => $_userId);
        $sql = "SELECT * FROM tbl_debt LEFT JOIN tbl_payment ON payment_id = debt_payment_id WHERE debt_deleted IS NULL AND debt_paid_date IS NULL AND payment_deleted IS NULL AND (payment_status = 'A' OR payment_status = 'P') AND payment_user_id = :userId ORDER BY payment_created";

        return $DBobject->wrappedSql($sql, $params);
    }

    /**
     * Get franchisees ids given the debt_id.
     *
     * @param int $_debtId
     *
     * @return array
     */
    public function getFranchiseesObjIdByDebtId($_debtId)
    {
        global $DBobject;

        $response = array();
        $params = array(':debt_id' => $_debtId);
        $sql = 'SELECT location_franchisee_id FROM tbl_debt LEFT JOIN tbl_payment ON payment_id = debt_payment_id
                LEFT JOIN tbl_cartitem ON cartitem_cart_id = payment_cart_id
                LEFT JOIN tbl_classterm ON classterm_id = cartitem_product_id
                LEFT JOIN tbl_class ON class_object_id = classterm_class_id
                LEFT JOIN tbl_location ON location_object_id = class_location_id
                WHERE debt_deleted IS NULL AND payment_deleted IS NULL AND cartitem_deleted IS NULL AND classterm_deleted IS NULL AND class_deleted IS NULL AND location_deleted IS NULL
                AND cartitem_class_flag = 1 AND class_published = 1 AND location_published = 1 AND debt_id = :debt_id';
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            foreach ($res as $r) {
                $response[] = $r['location_franchisee_id'];
            }
        }

        return $response;
    }

    /**
     * 微信支付成功(支付宝支付)，修改订单状态
     * //tbl_payment => status = A;tbl_order => insert new;tbl_enrolment => update publish =1
     */
    public function changPaySuccess($pay_type, $trade_no)
    {
        global $DBobject,$CONFIG;
        if ($pay_type=='wechatpay') {
            $sql = "SELECT * FROM tbl_payment WHERE payment_response_summary_code = :scode";
            $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
            if ($res) {
                //var_dump($res[0]);exit;
                $payment_id = $res[0]['payment_id'];
                $payment_no = $res[0]['payment_transaction_no'];
                $payment_total = $res[0]['payment_total'];
                $sql = "UPDATE tbl_payment SET payment_status = 'A' WHERE payment_response_summary_code = :scode";
                $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
                //SetOrderStatus 12 - A已完成 - 微信支付
                $pay_obj = new Bank([]);
                $pay_obj->SetOrderStatus($payment_id, 12);
                $this->setEnrolmentPublishByPaymentId($payment_id, 1);
                //异步邮件队列
                // $queue = new QueueClass;
                // $params = [
                //     'subject' => "订单支付成功 | 澳睿跑 (Ready Steady Go Kids)",
                //     'body' => "订单支付成功，订单号：{$payment_no}，订单金额：￥{$payment_total}",
                //     'to' => (string)$CONFIG->company->email,
                //     'from_name' => (string)$CONFIG->company->name,
                //     'from'=> (string)$CONFIG->company->email,
                // ];
                // $queue->add('sendMail', $params);
            }
        } elseif ($pay_type=='alipay') {
            $sql = "SELECT * FROM tbl_payment WHERE payment_response_summary_code = :scode";
            $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
            if ($res) {
                //var_dump($res[0]);exit;
                $payment_id = $res[0]['payment_id'];
                $payment_no = $res[0]['payment_transaction_no'];
                $payment_total = $res[0]['payment_total'];
                $sql = "UPDATE tbl_payment SET payment_status = 'A' WHERE payment_response_summary_code = :scode";
                $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
                //SetOrderStatus 13 - A已完成 - 支付宝支付
                $pay_obj = new Bank([]);
                $pay_obj->SetOrderStatus($payment_id, 14);
                $this->setEnrolmentPublishByPaymentId($payment_id, 1);
            }
            //异步更新一卡易会员信息队列
            //可以人工添加
            $subject = "订单支付成功 | 澳睿跑 (Ready Steady Go Kids)";
            $body = "订单支付成功，订单号：{$payment_no}，订单金额：￥{$payment_total}";
            sendMail((string)$CONFIG->company->email, (string)$CONFIG->company->name, (string)$CONFIG->company->email, $subject, $body);
        } elseif ($pay_type=='unipay') {
            $sql = "SELECT * FROM tbl_payment WHERE payment_response_summary_code = :scode";
            $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
            if ($res) {
                $payment_id = $res[0]['payment_id'];
                $payment_no = $res[0]['payment_transaction_no'];
                $payment_total = $res[0]['payment_total'];
                $sql = "UPDATE tbl_payment SET payment_status = 'A' WHERE payment_response_summary_code = :scode";
                $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
                //SetOrderStatus 15 - A已完成 - 银联支付
                $pay_obj = new Bank([]);
                $pay_obj->SetOrderStatus($payment_id, 16);
                $this->setEnrolmentPublishByPaymentId($payment_id, 1);
            }
            //异步更新一卡易会员信息队列
            //可以人工添加
            $subject = "订单支付成功 | 澳睿跑 (Ready Steady Go Kids)";
            $body = "订单支付成功，订单号：{$payment_no}，订单金额：￥{$payment_total}";
            sendMail((string)$CONFIG->company->email, (string)$CONFIG->company->name, (string)$CONFIG->company->email, $subject, $body);
        }
    }

    /**
     * 查询支付状态
     */
    public function getPaymentStatus($trade_no)
    {
        global $DBobject;
        if (!$trade_no) {
            return false;
        }
        $sql = "SELECT * FROM tbl_payment WHERE payment_response_summary_code = :scode";
        $res = $DBobject->wrappedSql($sql, [":scode" => $trade_no]);
        return $res;
    }
}
